﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using MINERP.DAL.Statistics;

namespace MINERP.BLL.Statistics
{
    public class ProductInfomationBLL
    {

        ComPerfDAL dal = new ComPerfDAL();
        public DataSet GetProductInfomation(DateTime inStartTime, DateTime inEndTime)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append(" select CreateDay,OrderProductId,ProductName,ProductCategoryName,OrderProductCode,CtmName,Width,Height,productNum,PrintCount,ProductCount,Areas,AllCount,shop.shopID as shopID,shop.shopName AS shopName,Shop.Area as Area,shop.Salesman as Salesman from ( ");
            stringBuilder.Append(" SELECT  CONVERT(datetime, ta.CreateDay) AS CreateDay, ta.shopid, ta.OrderProductId, ta.ProductName, ta.ProductCategoryName, ta.OrderProductCode,                                                                                                           ");
            stringBuilder.Append("                       ta.CtmName, ta.Width, ta.Height, ta.productNum, tb.PrintCount, tb.ProductCount, tb.Areas, tb.PrintCount + tb.ProductCount AS AllCount                                                                                                ");
            stringBuilder.Append(" FROM         (SELECT     CONVERT(varchar(10), product.OrderTime, 23) AS CreateDay, product.shopID, product.OrderProductId, Tb_Product.ProductName,                                                                                                         ");
            stringBuilder.Append("                                               Tb_PorductCategory.ProductCategoryName, product.OrderProductCode, product.CtmName, product.Width, product.Height,                                                                                            ");
            stringBuilder.Append("                                               product.productNum                                                                                                                                                                                           ");
            stringBuilder.Append("                        FROM          (SELECT     orderInfo_BK.OrderProductId, orderInfo_BK.ProductId, orderInfo_BK.OrderProductCode, orderInfo_BK.CtmName,                                                                                                 ");
            stringBuilder.Append("                                                                       orderInfo_BK.Width, orderInfo_BK.Height, orderInfo_BK.productNum, Tb_OrderBag_BK.OrderTime,                                                                                          ");
            stringBuilder.Append("                                                                       Tb_OrderBag_BK.shopID                                                                                                                                                                ");
            stringBuilder.Append("                                                FROM          orderInfo_BK INNER JOIN                                                                                                                                                                       ");
            stringBuilder.Append("                                                                       Tb_OrderBag_BK ON orderInfo_BK.OrderBagId = Tb_OrderBag_BK.OrderBagId                                                                                                                ");
            stringBuilder.Append("                                                UNION                                                                                                                                                                                                       ");
            stringBuilder.Append("                                                SELECT     orderInfo.OrderProductId, orderInfo.ProductId, orderInfo.OrderProductCode, orderInfo.CtmName, orderInfo.Width, orderInfo.Height,                                                                 ");
            stringBuilder.Append("                                                                      orderInfo.productNum, Tb_OrderBag.OrderTime, Tb_OrderBag.shopID                                                                                                                       ");
            stringBuilder.Append("                                                FROM         orderInfo INNER JOIN                                                                                                                                                                           ");
            stringBuilder.Append("                                                                      Tb_OrderBag ON orderInfo.OrderBagId = Tb_OrderBag.OrderBagId) AS product INNER JOIN                                                                                                   ");
            stringBuilder.Append("                                               Tb_Product ON product.ProductId = Tb_Product.ProuctId INNER JOIN                                                                                                                                             ");
            stringBuilder.Append("                                               Tb_PorductCategory ON Tb_Product.ProductCategoryId = Tb_PorductCategory.ProductCategoryId) AS ta INNER JOIN                                                                                                  ");
            stringBuilder.Append("                           (SELECT     ty.PrintCount, ty.ProductCount, ty.Areas, Bill_Detail.OrderInfoCode                                                                                                                                                  ");
            stringBuilder.Append("                             FROM          (SELECT     SUM(PrintCount) AS PrintCount, SUM(ProductCount) AS ProductCount, SUM(Area) AS Areas, BillDetailId                                                                                                   ");
            stringBuilder.Append("                                                     FROM          (SELECT     ISNULL(CASE Type WHEN 1 THEN count END, 0) AS PrintCount, ISNULL(CASE Type WHEN 0 THEN count END, 0)                                                                         ");
            stringBuilder.Append("                                                                                                    AS ProductCount, ISNULL(CASE Type WHEN 2 THEN count END, 0) AS DesignCount, Area, BillDetailId                                                          ");
            stringBuilder.Append("                                                                             FROM          Bill_Detail_Detail) AS tz                                                                                                                                        ");
            stringBuilder.Append("                                                     GROUP BY BillDetailId) AS ty INNER JOIN                                                                                                                                                                ");
            stringBuilder.Append("                                                    Bill_Detail ON ty.BillDetailId = Bill_Detail.BillDetailId) AS tb ON ta.OrderProductCode = tb.OrderInfoCode                                                                                              ");
            stringBuilder.Append(" WHERE     (ta.CreateDay BETWEEN CONVERT(datetime, '"+inStartTime+"') AND CONVERT(datetime, '"+inEndTime+"')) ) as tbb,shop where shop.shopid=tbb.shopID                                                                                             ");
            return dal.ExceSQLDataSet(stringBuilder.ToString());
        }


    }
}
